package com.cpi.cornpp.customer.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.customer.dto.CustomerDTO;
import com.cpi.cornpp.customer.dto.CustomerForecastDTO;
import com.cpi.cornpp.customer.exception.CustomerException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
import com.cs.common.utils.StringUtils;

public class CustomerDAO extends DBDAO {
	private static final String SQL_SELECT_CUSTOMERS = "SELECT customer_id, description, type, group_id FROM vw_customers ";//$NON-NLS-1$
	private static final String SQL_SELECT_CUSTOMERS_BY_ID_FILTER = " customer_id like ? ";//$NON-NLS-1$
	private static final String SQL_SELECT_CUSTOMERS_BY_DESCRIPTION_FILTER = " description like ? ";//$NON-NLS-1$
	private static final String WHERE = " where ";//$NON-NLS-1$
	private static final String SQL_SELECT_CUSTOMER_BY_ID = "SELECT customer_id, description, type, group_id FROM vw_customers WHERE customer_id = ? ";//$NON-NLS-1$
	private static final String OR = " or ";//$NON-NLS-1$
	private static final String SQL_SELECT_CUSTOMERS_BY_GROUP = "select customer_id, round( sum(corn_contracted)/?* ?, 0 ), sum(corn_contracted)-(select sum(isnull(id.quantity,0)) from dbo.purchases_instructions_futures pif left join realizeds_instructions ri on  pif.purchase_instruction_future_id = ri.purchase_instruction_future_id  left join corn_futures cf on  ri.corn_future_id = cf.corn_future_id  left join dbo.corn_futures_distributions cfd on cf.corn_future_id = cfd.corn_future_id and cfd.customer_id = vw_global_instructions_forecasts.customer_id left join dbo.items_distributions id on cfd.corn_future_distribution_id = id.corn_future_distribution_id where pif.purchase_instruction_detail_id = ?) from vw_global_instructions_forecasts where month_period_id in ( select month_period_id from instructions_forecasts where purchase_instruction_detail_id = ? ) and item_id = ? group by customer_id ";//$NON-NLS-1$
	
	public CustomerDTO getCustomerById(CustomerDTO cusDTO)
			throws CustomerException {
		CustomerDTO customerDTO = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {

			if (cusDTO != null) {
				pstm = prepareSQL(SQL_SELECT_CUSTOMER_BY_ID, dsName);

				pstm.setString(1, cusDTO.getCustomerId());

				rs = executeQuery(pstm);
				if( rs.next() ){
					customerDTO = new CustomerDTO();
					customerDTO.setCustomerId(rs.getString(1));
					customerDTO.setDescription(rs.getString(2));
					customerDTO.setType(rs.getString(3));
					customerDTO.setGroupId(rs.getString(4));
				}
			}
		} catch (PoolException poolException) {
			CustomerException customerException = null;
			poolException.printStackTrace(System.out);
			customerException = new CustomerException(poolException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw customerException;
		} catch (SQLException sqlException) {
			CustomerException customerException = null;
			sqlException.printStackTrace(System.out);
			customerException = new CustomerException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw customerException;
		} finally {
			try {

				closeConnection(pstm.getConnection());

			} catch (SQLException sqle) {

			}
		}

		return customerDTO;
	}
	/**
	 * Parametros para la consulta
	 * 1 - Cantidad total
	 * 2 - Contratos
	 * 3 - Purchase detail id
	 * 4 - Item id 
	 */
	public List getCustomersForListOfGroup(CustomerForecastDTO customerForecastDTO) throws CustomerException {
		List customerDTOs = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		CustomerForecastDTO customerForecastDTOResult = null;
		try{
			customerDTOs = new ArrayList(0);
			preparedStatement = prepareSQL(SQL_SELECT_CUSTOMERS_BY_GROUP, dsName);
			preparedStatement.setDouble(1, customerForecastDTO.getCornContracted());
			preparedStatement.setInt(2, customerForecastDTO.getContracts());
			preparedStatement.setInt(3, customerForecastDTO.getPurchaseInstructionDetailId());
			preparedStatement.setInt(4, customerForecastDTO.getPurchaseInstructionDetailId());
			preparedStatement.setString(5, customerForecastDTO.getItemId());
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				customerForecastDTOResult = new CustomerForecastDTO();
				customerForecastDTOResult.setCustomerId(resultSet.getString(1));
				customerForecastDTOResult.setContracts(resultSet.getInt(2));
				customerForecastDTOResult.setRemainingTons(resultSet.getDouble(3));
				customerForecastDTOResult.setItemId(customerForecastDTO.getItemId());
				customerDTOs.add(customerForecastDTOResult);
			}
		} catch (PoolException poolException) {
			CustomerException customerException = null;
			poolException.printStackTrace(System.out);
			customerException = new CustomerException(poolException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw customerException;
		} catch (SQLException sqlException) {
			CustomerException customerException = null;
			sqlException.printStackTrace(System.out);
			customerException = new CustomerException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw customerException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (SQLException sqlException) {}
		}
		return customerDTOs;
	}

	public List getCustomers(CustomerDTO dto) throws CustomerException {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List customerList = null;
		StringBuffer query = null;
		boolean where = false;
		boolean idFilter = false;
		boolean desFilter = false;
		int idIndex = 0;
		int desIndex = 0;

		try {

			query = new StringBuffer(0);
			query.append(SQL_SELECT_CUSTOMERS);
			if (!StringUtils.emptyString(dto.getDescription())) {
				query.append(WHERE);
				query.append(SQL_SELECT_CUSTOMERS_BY_ID_FILTER);
				where = true;
				idFilter = true;
				idIndex++;
			}
			if (dto.getDescription() != null
					&& !StringUtils.emptyString(dto.getDescription())) {
				if (!where)
					query.append(WHERE);
				else
					query.append(OR);

				query.append(SQL_SELECT_CUSTOMERS_BY_DESCRIPTION_FILTER);

				desFilter = true;
			}
			pstm = prepareSQL(query.toString(), dsName);
			if (idFilter) {
				desIndex++;
				pstm.setString(desIndex, dto.getDescription());
			}
			if (desFilter) {
				desIndex++;
				pstm.setString(desIndex, dto.getDescription());
			}

			rs = executeQuery(pstm);
			customerList = mapRows(rs);

		} catch (PoolException poolException) {
			CustomerException customerException = null;
			poolException.printStackTrace(System.out);
			customerException = new CustomerException(poolException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw customerException;
		} catch (SQLException sqlException) {
			CustomerException customerException = null;
			sqlException.printStackTrace(System.out);
			customerException = new CustomerException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw customerException;
		} finally {
			try {

				closeConnection(pstm.getConnection());

			} catch (Exception sqle) {

			}
		}

		return customerList;
	}

	private List mapRows(ResultSet rs) throws SQLException {
		List customerList = null;

		customerList = new ArrayList(0);

		if (rs != null) {

			for (; rs.next();) {
				customerList.add(mapRow(rs, rs.getRow()));
			}
		}

		return customerList;
	}

	private CustomerDTO mapRow(ResultSet rs, int rowIndex) throws SQLException {
		CustomerDTO customerDTO = null;

		if ((rs != null) && (rs.absolute(rowIndex))) {
			customerDTO = new CustomerDTO();

			customerDTO.setCustomerId(rs.getString(1));
			customerDTO.setDescription(rs.getString(2));
			customerDTO.setType(rs.getString(3));
			customerDTO.setGroupId(rs.getString(4));
			
		}

		return customerDTO;
	}
}